<?php

namespace app\web\controller;


use think\Db;

/**
 * 设置活动
 * Class Combine
 * @package app\manage\admin
 */
class ReportStatistics extends Auth
{

    /**
     * 房间使用情况分析
     */
    public function room_usage_analysis()
    {

        $post = $this->request->post();
        $query = Db::name('qt_room_appointment')->alias('ra')
            ->field([
                'ra.id',
                'ro.name',
                'ro.id',
                'ro.bed_num',
                'count(ra.id) as book_num',
            ])
            ->leftJoin('qt_room ro', 'ra.room_id=ro.id')
            ->where('ra.store_id', $this->userInfo['store_id']);

        if (isset($post['start_time']) && !empty($post['start_time'])) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
            $query->whereBetweenTime('ra.created_time', $startTime, $endTime);
        }

        $appointment = $query->group('ro.bed_num')->select();

        if (empty($appointment)) {
            $this->result('查询成功', [], 200);
        }

        $data = [];
        $data['total_num'] = array_sum(array_column($appointment, 'book_num'));
        $data['one'] = 0;
        $data['two'] = 0;
        $data['three'] = 0;
        $data['many'] = 0;

        foreach ($appointment as $key => $val) {

            switch ($val['bed_num']) {
                case 1 :
                    $data['one'] = $data['one'] + $val['book_num'];
                    break;
                case 2 :
                    $data['two'] = $data['two'] + $val['book_num'];
                    break;
                case 3:
                    $data['three'] = $data['three'] + $val['book_num'];
                    break;
                case $val['bed_num'] > 3:
                    $data['many'] = $val['book_num'] + $data['many'];
                    break;
            }
        }


        $data['proportion']['one_proportion'] = round(($data['one'] / $data['total_num']) * 100);
        $data['proportion']['two_proportion'] = round(($data['two'] / $data['total_num']) * 100);
        $data['proportion']['three_proportion'] = round(($data['three'] / $data['total_num']) * 100);
        $data['proportion']['many_proportion'] = round(($data['many'] / $data['total_num']) * 100);

        $this->result('查询成功', $data, 200);

    }


    /**
     * PA净房统计
     */
    public function pa_analysis()
    {

        $post = $this->request->post();
        $limit = $post['limit'] ?? 10;

        $query = Db::name('qt_room_clear')
            ->alias('c')
            ->field('c.clear_user_id,c.total_commission,c.clear_bed,u.real_name,u.work_number')
            ->group('c.clear_user_id')
            ->leftJoin('qt_users u', 'c.clear_user_id=u.id')
            ->where('c.store_id', $this->userInfo['store_id']);


        if (isset($post['start_time']) && !empty($post['start_time'])) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
            $query->whereBetweenTime('c.clear_time', $startTime, $endTime);
        }

        if (!empty($post['work_number'])) {
            $query->where('u.work_number', $post['work_number']);
        }

        $clear_data = $query->paginate($limit)->each(function ($item) {

            $room_type = Db::name('qt_room_clear')
                ->alias('c')
                ->field('r.type_id,t.name,count(c.id) as num')
                ->leftJoin('qt_room r', 'c.room_id=r.id')
                ->leftJoin('qt_room_type t', 'r.type_id=t.id')
                ->where('c.clear_user_id', $item['clear_user_id'])
                ->whereNotNull('r.type_id')
                ->group('r.type_id')
                ->select();

            $room_type_str = '';
            foreach ($room_type as $key => $val) {
                $room_type_str .= $val['name'] . '(' . $val['num'] . ') ';
            }

            $room_data = Db::name('qt_room_clear')->alias('c')
                ->field('c.room_id,r.name,count(c.id) as num')
                ->leftJoin('qt_room r', 'c.room_id=r.id')
                ->whereNotNull('r.id')
                ->where('c.clear_user_id', $item['clear_user_id'])
                ->group('c.room_id')
                ->select();

            $room_info_str = '';
            foreach ($room_data as $key => $val) {
                $room_info_str .= $val['name'] . '(' . $val['num'] . ') ';
            }


            $item['room_number'] = !empty($room_data) ? array_sum(array_column($room_data, 'num')) : 0;
            $item['clear_bed_number'] = Db::name('qt_room_clear')->where('clear_user_id', $item['clear_user_id'])->sum('clear_bed') ?? 0;
            $item['room_type'] = $room_type_str;
            $item['room_info'] = $room_info_str;
            return $item;
        });

        $this->result('查询成功', $clear_data, 200);

    }


    /**
     * 设置PA提成
     */
    public function set_pa_analysis()
    {

        if ($this->request->isPost()) {
            $post = $this->request->post();
            if (empty($post['data'])) {
                $this->result('参数错误', '', 204);
            }
            $data = json_decode($post['data'], true);
            foreach ($data as $key => $val) {
                $room = Db::name('qt_room')->where('id', $val['id'])->find();
                if (empty($room)) {
                    continue;
                }
                Db::name('qt_room')->where('id', $val['id'])->update(['clean_price' => $val['clean_price']]);
            }

            $this->result('操作成功', '', 200);
        } else {
            $this->result('查询失败', '', 204);
        }
    }

    /**
     * 获取pa提成
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function get_pa_info()
    {

        $room_data = Db::name('qt_room')->alias('r')
            ->field([
                'r.id',
                'r.name',
                'r.bed_num',
                'r.clean_price',
                't.name type_name',
            ])
            ->leftJoin('qt_room_type t', 'r.type_id=t.id')
            ->select();

        $this->result('查询成功', $room_data, 200);
    }

    /**
     * 会员次卡统计报表
     */
    public function viper_card_use_log()
    {

        $post = $this->request->post();
        $limit = !empty($post['limit']) ? $post['limit'] : 10;
        $query = Db::name('qt_viper_card_recharge_log')
            ->alias('log')
            ->field([
                'log.id',
                'log.viper_card_id',
                'log.store_id',
                'log.recharge_tempid',
                'card.name',
                'card.card_no',
                'card.mobile',
            ])
            ->leftJoin('qt_viper_card card', 'log.viper_card_id = card.id')
            //->leftJoin('qt_viper_card_item item','item.viper_card_id = card.id')
            ->where([
                'log.is_revoke' => 2,//是否撤销
                'log.card_type' => 2//次卡
            ])->where('log.store_id', $this->userInfo['store_id']);

        if (isset($post['start_time']) && !empty($post['start_time'])) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
            $query->whereBetweenTime('log.create_time', $startTime, $endTime);
        }
        $query->limit($limit)->order('id desc');
        $card_type_data = $query->paginate()->each(function ($val) {

            //使用记录
            $log_data = Db::name('qt_viper_card_use_log')->where([
                'viper_card_id' => $val['viper_card_id'],
                'template_id' => $val['recharge_tempid'],
                'card_type' => 1//次卡
            ])->select();

            //包含的项目
            $have_product = Db::name('qt_viper_number_card_template_item')
                ->alias('ti')
                ->leftJoin('qt_project p', 'p.id = ti.item_id')
                ->where('ti.number_card_template_id', $val['recharge_tempid'])
                ->where('ti.item_type', 'project')
                ->whereNotNull('p.id')
                ->column('p.name');

            //使用的项目
            $use_product = Db::name('qt_project')->alias('o')
                ->whereIn('id', array_column($log_data, 'item_id'))
                ->column('name');

            //剩余次数
            $card_item = Db::name('qt_viper_card_item')
                ->where('viper_card_id', $val['viper_card_id'])
                ->where('number_template_id', $val['recharge_tempid'])
                ->find();
            $val['surplus_num'] = empty($card_item) ? 0 : $card_item['total_number'] - $card_item['use_number'];
            $val['have_product'] = $have_product;
            $val['use_product'] = $use_product;

            return $val;
        });


        $this->result('查询成功', $card_type_data, 200);

    }


    /**
     * 订房提成分析
     */
    public function room_appointment()
    {

        $post = $this->request->post();
        $query = Db::name('qt_room_book')
            ->field('count(id) as num,sum(total_commission) as total_commission,sale_id')
            ->group('sale_id')->where('store_id', $this->userInfo['store_id']);

        if (isset($post['start_time']) && !empty($post['start_time'])) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
            $query->whereBetweenTime('create_time', $startTime, $endTime);
        }

        if (!empty($post['sale_type'])) {
            $query->where('sale_type',$post['sale_type']);
        }
        if (!empty($post['sale_id'])) {
            $query->where('sale_id', $post['sale_id']);
        }

        $query->limit(10)->order('sale_id asc');
        $card_type_data = $query->paginate()->each(function ($item) {

            $arrive_num = Db::name('qt_room_book')->where('sale_id', $item['sale_id'])->where('status', 3)->count();
            $reserve_num = Db::name('qt_room_book')->where('sale_id', $item['sale_id'])->count();
            $item['arrive_num'] = $arrive_num;
            $item['reserve_num'] = $reserve_num;
            $item['user_name'] = Db::name('qt_users')->where('id', $item['sale_id'])->value('real_name') ?? '';
            $type = Db::name('qt_users')->where('id', $item['sale_id'])->value('group');
            $item['sale_type_val'] = $type == 1 ? '技师' : '楼面';
            return $item;

        });

        $this->result('查询成功', $card_type_data, 200);
    }


    /**
     * 上钟明细列表
     */
    public function clock_info()
    {
        $post = $this->request->post();
        if(!isset($post['type']) || empty($post['type'])){
            $this->result('缺少查询方式', '', 204);
        }
        if($post['type'] == '1'){
            $field = 'o.created_time';
            $orderField = 'created_time';
        }else{
            $field = 'o.pay_time';
            $orderField = 'updated_time';
        }
        $sql = Db::name('qt_order_project')
            ->alias('o')
            ->leftJoin('qt_users u','u.id = o.engineer_id')
            ->field([
                'o.engineer_id',
                'sum(o.commission) as commission',
                'count(case when o.order_type=1 or o.order_type=4 then 1  end) as wheel',
                'count(case when o.order_type=2 then 1 end) as o_clock',
                'count(case when o.order_type=3 then 1 end) as call_clock',
                'count(case when o.order_type=5 then 1 end) as add_clock',
                'count(case when o.is_add=2 then 1 end) as is_add',
                'u.id'
            ])
            ->where(['o.store_id' => $this->userInfo['store_id'],'o.is_delete'=>'2'])
            ->whereNotNull('o.engineer_id')
            ->where('o.engineer_id','neq','0')
            ->whereBetweenTime($field,strtotime($post['start_time']),strtotime($post['end_time']));
        //工种筛选
        if (!empty($post['work_type'])) {
            //获取工种所有的用户
            $workType = Db::name('qt_work_type')->where('id', $post['work_type'])->find();
            if ($workType['pid'] == '0') {
                //顶级分类查询所有的下级
                $workRes = Db::name('qt_work_type')->where('pid', $post['work_type'])->column('id');
                $workTypeWhere[0] = $workType['id'];
                $workRes = array_merge($workRes, $workTypeWhere);
                $users = Db::name('qt_users')->whereIn('work_type', $workRes)->column('id');
                $sql->whereIn('u.id', $users);
            } else {
                $users = Db::name('qt_users')->where('work_type', $post['work_type'])->column('id');
                $sql->whereIn('u.id', $users);
            }
        }
        $order = $sql->group('o.engineer_id')
            ->paginate()->each(function ($item) use ($post,$orderField) {
                $project = Db::name('qt_project')->alias('p')
                    ->field([
                        'count(case when type=1 then 1 end) as main_item',
                        'count(case when type=2 then 1 end) as adjunct',
                    ])
                    ->leftJoin('qt_order_project o', 'o.project_id=p.id')
                    ->where([
                        'o.engineer_id' => $item['engineer_id'],
                        'o.is_delete'=>'2'
                    ])->group('p.type')->find();

                $project_item = Db::name('qt_project')->alias('p')
                    ->field([
                        'p.id',
                        'p.name',
                        'count(p.id) as num',
                    ])
                    ->leftJoin('qt_order_project o', 'o.project_id=p.id')
                    ->where([
                        'o.engineer_id' => $item['engineer_id'],
                        'o.is_delete'=>'2'
                    ])->group('p.name')
                    ->select();
                foreach ($project_item as $key=>$value){
                    $projectDetail = Db::name('qt_order_project')
                        ->field([
                            'name as project_name',
                            'count(case when order_type=1 then 1  end) as wheel',
                            'count(case when order_type=2 then 1 end) as o_clock',
                            'count(case when order_type=3 then 1 end) as call_clock',
                            'count(case when order_type=4 then 1 end) as y_wheel',
                            'count(case when order_type=5 then 1 end) as add_clock',
                            'count(case when is_add=1 then 1 end) as is_add',
                        ])
                        ->where(['project_id'=>$value['id'],'engineer_id'=>$item['engineer_id'],'is_delete'=>'2'])
                        ->whereBetweenTime($orderField,strtotime($post['start_time']),strtotime($post['end_time']))
                        ->select();
                }
                $item['project'] = $project;
                $item['project_item'] = $project_item;
                $item['project_detail'] = $projectDetail;
                $engineer_data = Db::name('qt_users')->where('id', $item['engineer_id'])->find();//技师
                $item['user_name'] = $engineer_data['real_name'] ?? '';
                $item['work_number'] = $engineer_data['work_number'] ?? '';
                $item['commission'] = Db::name('qt_commission_log')
                    ->where(['store_id'=> $this->userInfo['store_id'],'user_id'=>$item['engineer_id']])
                    ->whereIn('type',['1','6'])
                    ->whereBetweenTime($orderField,strtotime($post['start_time']),strtotime($post['end_time']))
                    ->sum('commission');
                return $item;

            });

        $this->result('查询成功', $order, 200);
    }


    /**
     * 技师汇总报表2
     */
    public function engineer_report_two()
    {
        $post = $this->request->post();
        if (empty($post['start_time']) || empty($post['end_time'])) {
            $this->result('缺少时间类型', '', 204);
        }
        if (isset($getData['type']) && $getData['type'] == '1') {
            $field = 'created_time';
        } else {
            $field = 'updated_time';
        }
        $sql = Db::name('qt_commission_log')->alias('log')
            ->field('u.id,u.real_name as is_user_name,u.work_number as is_user_work_number')
            ->leftJoin('qt_users u', 'u.id = log.user_id')
            ->group('log.user_id')
            ->where('log.user_id', 'neq', '0')
            ->whereBetweenTime('log.' . $field, $post['start_time'], $post['end_time']);
        //技师号筛选
        if (isset($post['work_number']) && !empty($post['work_number'])) {
            $sql->where(['u.work_number' => $post['work_number']]);
        }
        //工种筛选
        if (isset($post['work_type']) && !empty($getData['work_type'])) {
            //获取工种所有的用户
            $workType = Db::name('qt_work_type')->where('id', $post['work_type'])->find();
            if ($workType['pid'] == '0') {
                //顶级分类查询所有的下级
                $workRes = Db::name('qt_work_type')->where('pid', $post['work_type'])->column('id');
                $workTypeWhere[0] = $workType['id'];
                $workRes = array_merge($workRes, $workTypeWhere);
                $users = Db::name('qt_users')->whereIn('top_type_id', $workRes)->column('id');
                $sql->whereIn('u.user_id', $users);
            } else {
                $users = Db::name('qt_users')->where('top_type_id', $post['work_type'])->column('id');
                $sql->whereIn('u.user_id', $users);
            }
        }
        $result = $sql->select();
        foreach ($result as $key => $value) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
            $query = Db::name('qt_commission_log')
                ->field('count(id) as number,status')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'is_add' => '1'])
                ->group('status')
                ->select();
            //总钟数
            $result[$key]['is_total_clock'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'is_add' => '1'])
                ->count();
            //副项
            $result[$key]['is_subitem'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '6', 'is_add' => '1'])
                ->count();
            $result[$key]['is_row'] = 0;
            $result[$key]['is_spot'] = 0;
            $result[$key]['is_add'] = 0;
            $result[$key]['is_call'] = 0;
            $result[$key]['is_choose'] = 0;
            foreach ($query as $k => $v) {
                if ($v['status'] == '1') {
                    $result[$key]['is_row'] = $v['number'];
                }
                if ($v['status'] == '2') {
                    $result[$key]['is_spot'] = $v['number'];
                }
                if ($v['status'] == '3') {
                    $result[$key]['is_call'] = $v['number'];
                }
                if ($v['status'] == '4') {
                    $result[$key]['is_choose'] = $v['number'];
                }
                if ($v['status'] == '5') {
                    $result[$key]['is_add'] = $v['number'];
                }

            }
            //计算加钟数据
            $addQueue = Db::name('qt_commission_log')
                ->field('count(id) as number,status')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'is_add' => '2'])
                ->group('status')
                ->select();
            $result[$key]['is_add_row'] = 0;
            $result[$key]['is_add_spot'] = 0;
            $result[$key]['is_add_add'] = 0;
            $result[$key]['is_add_choose'] = 0;
            $result[$key]['is_add_call'] = 0;
            foreach ($addQueue as $k => $v) {
                if ($v['status'] == '1') {
                    $result[$key]['is_add_row'] = $v['number'];
                }
                if ($v['status'] == '2') {
                    $result[$key]['is_add_spot'] = $v['number'];
                }
                if ($v['status'] == '3') {
                    $result[$key]['is_add_call'] = $v['number'];
                }
                if ($v['status'] == '4') {
                    $result[$key]['is_add_choose'] = $v['number'];
                }
                if ($v['status'] == '5') {
                    $result[$key]['is_add_add'] = $v['number'];
                }

            }
            $result[$key]['is_call'] = $result[$key]['is_call'] + $result[$key]['is_add_call'];
            //售卡业绩
            $result[$key]['is_card_achievement'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id']])
                ->whereIn('type', ['3', '4', '5', '7'])
                ->sum('payment_price');
            //售卡提成
            $result[$key]['is_card_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id']])
                ->whereIn('type', ['3', '4', '5', '7'])
                ->sum('commission');

            //商品提成
            $result[$key]['is_goods_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '2'])
                ->sum('commission');
            //加钟提成
            $result[$key]['is_add_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'status' => '5'])
                ->sum('commission');
            //call提成
            $result[$key]['is_call_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'status' => '3'])
                ->sum('commission');
            //选钟提成
            $result[$key]['is_choose_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '1', 'status' => '4'])
                ->sum('commission');
            //选钟提成
            $result[$key]['is_subitem_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '6'])
                ->sum('commission');
            //技师推荐提成
            $result[$key]['is_user_recommend'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id'], 'type' => '8'])
                ->sum('commission');
            //技师推荐提成
            $result[$key]['is_total_commission'] = Db::name('qt_commission_log')
                ->whereBetweenTime('created_time', $startTime, $endTime)
                ->where(['user_id' => $value['id']])
                ->sum('commission');
        }
        $this->result('查询成功', $result, 200);
    }

    /**
     * 会员消费，充值分类统计
     */
    public function viper_use_log()
    {

        $post = $this->request->post();
        $startTime = 0;
        $endTime = 0;
        if (isset($post['start_time']) && !empty($post['end_time'])) {
            $startTime = strtotime($post['start_time']);
            $endTime = strtotime($post['end_time']);
        }
        $viper_type = Db::name('qt_viper_type')->field(['id', 'name'])->where(['store_id' => $this->userInfo['store_id']])->select();
        foreach ($viper_type as $key => &$val) {

            $query1 = Db::name('qt_viper_card_recharge_log')->alias('log')
                ->field([
                    'sum(log.amount) as amount',
                    'sum(log.give_amount) as give_amount',
                    'sum(log.card_amount) as card_amount',
                ])
                ->leftJoin('qt_viper_card card', 'log.viper_card_id=card.id')
                ->where('card.viper_type_id', $val['id']);
            if ($startTime) $query1->whereBetweenTime('log.create_time', $startTime, $endTime);
            $log_data = $query1->find();


            $query2 = Db::name('qt_viper_card')
                ->field([
                    'sum(amount) as amount',
                    'sum(give_amount) as give_amount',
                ])
                ->where('viper_type_id', $val['id']);
            if ($startTime) $query2->whereBetweenTime('create_time', $startTime, $endTime);
            $viper_card = $query2->find();

            $user_pay = $log_data['amount'] - $viper_card['amount'];
            $give_pay = $log_data['give_amount'] - $viper_card['give_amount'];

            //划卡次数
            $query3 = Db::name('qt_viper_card_use_log')->alias('log')
                ->leftJoin('qt_viper_card card', 'log.viper_card_id=card.id')
                ->where('card.viper_type_id', $val['id']);
            if ($startTime) $query3->whereBetweenTime('log.create_time', $startTime, $endTime);
            $use_card_num = $query3->count('log.id');

            //查类型的会员卡
            $query4 = Db::name('qt_check_out_pay')->alias('pay')
                ->leftJoin('qt_viper_card card', 'pay.viper_card_id=card.id')
                ->where('card.viper_type_id', $val['id'])
                ->whereIn('pay.type', [1, 3]);
            if ($startTime)  $query4->whereBetweenTime('pay.create_time', $startTime, $endTime);
            $out_pay_data = $query4->select();

            $val['total_amount'] = $log_data['amount'];//充值合计
            $val['total_give_amount'] = $log_data['give_amount'];//赠送合计
            $val['total_card_amount'] = $log_data['card_amount'];//入卡合计
            $val['amount'] = $viper_card['amount'];//会员支付
            $val['give_amount'] = $viper_card['give_amount'];//会员赠送
            $val['use_card_num'] = $use_card_num;
            $val['user_pay'] = $user_pay < 0 ? 0 : $user_pay;
            $val['give_pay'] = $give_pay < 0 ? 0 : $give_pay;
            $val['cash_pay'] = 0;
            $val['wx_pay'] = 0;
            $val['union_pay'] = 0;
            $val['ali_pay'] = 0;
            $val['free_pay'] = 0;
            foreach ($out_pay_data as $k => $v) {
                switch ($v['pay_name']) {
                    case '现金':
                        $val['cash_pay'] += $val['cash_pay'];
                        break;
                    case '银联':
                        $val['union_pay'] += $val['union_pay'];
                        break;
                    case '微信':
                        $val['wx_pay'] += $val['wx_pay'];
                        break;
                    case '支付宝':
                        $val['ali_pay'] += $val['ali_pay'];
                        break;
                    case '免单':
                        $val['free_pay'] = 0;
                }
            }
        }

        $this->result('查询成功', $viper_type, 200);
    }


    /**
     * 外卡使用记录
     */
    public function wild_card_log()
    {


    }


}